/**
 * www.easyplatform.cn ©2016
 */
package cn.easyplatform.studio.web.editors.workbench;

import cn.easyplatform.entities.EntityInfo;
import cn.easyplatform.entities.beans.project.ProjectBean;
import cn.easyplatform.entities.beans.table.TableBean;
import cn.easyplatform.entities.beans.table.TableField;
import cn.easyplatform.entities.beans.table.TableFk;
import cn.easyplatform.entities.beans.table.TableIndex;
import cn.easyplatform.entities.transform.TransformerFactory;
import cn.easyplatform.lang.Strings;
import cn.easyplatform.lang.stream.StringOutputStream;
import cn.easyplatform.studio.StudioApp;
import cn.easyplatform.studio.cmd.biz.*;
import cn.easyplatform.studio.cmd.entity.CheckCmd;
import cn.easyplatform.studio.cmd.entity.QueryModelCmd;
import cn.easyplatform.studio.cmd.entity.SaveCmd;
import cn.easyplatform.studio.cmd.entity.ScanCmd;
import cn.easyplatform.studio.context.Contexts;
import cn.easyplatform.studio.utils.ExportExcel;
import cn.easyplatform.studio.utils.ImportExecl;
import cn.easyplatform.studio.utils.StudioUtil;
import cn.easyplatform.studio.utils.WebUtils;
import cn.easyplatform.studio.vos.*;
import cn.easyplatform.studio.web.editors.AbstractPanelEditor;
import cn.easyplatform.studio.web.editors.Editor;
import cn.easyplatform.studio.web.editors.EditorCallback;
import cn.easyplatform.studio.web.layout.WorkbenchController;
import cn.easyplatform.studio.web.views.impl.AbstractView;
import cn.easyplatform.type.EntityType;
import cn.easyplatform.type.FieldType;
import cn.easyplatform.type.ModeType;
import cn.easyplatform.type.ModeType;
import org.apache.commons.lang3.StringUtils;
import org.zkoss.poi.hssf.usermodel.HSSFWorkbook;
import org.zkoss.util.resource.Labels;
import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.Page;
import org.zkoss.zk.ui.event.*;
import org.zkoss.zk.ui.util.Clients;
import org.zkoss.zul.*;
import org.zkoss.zul.event.PagingEvent;
import org.zkoss.zul.event.ZulEvents;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author <a href="mailto:shiny_vc@163.com">陈云亮</a> <br/>
 * @since 2.0.0 <br/>
 */
public class TableEditor extends AbstractPanelEditor {

    private Listbox tables;

    private Button create;

    private Button buildTable;

    private Button buildKey;

    private Button buildIndex;

    private Button buildFk;

    private Button revert;

    private Button delete;

    private Button scanAll;

    private Button importExcel;

    private Button exportExcel;

    private Button exportTemp;

    private Grid sourceTable;

    private Grid targetTable;

    private Bandbox search;

    private Paging paging;

    private Combobox datasource;

    private LoginUserVo loginUserVo;

    /**
     * @param workbench
     */
    public TableEditor(WorkbenchController workbench, String id) {
        super(workbench, id);
        this.loginUserVo = Contexts.getUser();
    }

    @Override
    public void create(Object... args) {
        Idspace is = createPanel(Labels.getLabel("menu.table.refactor"), "~./images/table.gif", "~./include/editor/workbench/table.zul");
        for (Component comp : is.getFellows()) {
            if (comp.getId().equals("workbenchTable_listbox_tables")) {
                tables = (Listbox) comp;
                tables.addEventListener(Events.ON_SELECT, this);
            } else if (comp.getId().equals("paging")) {
                paging = (Paging) comp;
                paging.addEventListener(ZulEvents.ON_PAGING, this);
            } else if (comp.getId().equals("workbenchTable_combobox_datasource")) {
                datasource = (Combobox) comp;
                List<EntityVo> ds = StudioApp.execute(new QueryModelCmd(
                        EntityType.DATASOURCE.getName()));
                ProjectBean pb = Contexts.getProject();
                for (EntityVo vo : ds) {
                    Comboitem ci = new Comboitem(vo.getId());
                    ci.setDescription(vo.getName());
                    datasource.appendChild(ci);
                    if (pb.getBizDb().equals(vo.getId()))
                        datasource.setSelectedItem(ci);
                }
                datasource.addEventListener(Events.ON_CHANGE, this);
            } else if (comp.getId().equals("workbenchTable_grid_sourceTable")) {
                sourceTable = (Grid) comp;
            } else if (comp.getId().equals("workbenchTable_grid_targetTable")) {
                targetTable = (Grid) comp;
            } else if (comp instanceof Button || comp instanceof A) {
                if (comp.getId().equals("workbenchTable_toolbarbutton_create")) {
                    create = (Button) comp;
                    if (!loginUserVo.isAuthorized("tableCreate")) {
                        create.setDisabled(true);
                    }
                } else if (comp.getId().equals("workbenchTable_toolbarbutton_buildTable")) {
                    buildTable = (Button) comp;
                    if (!loginUserVo.isAuthorized("tableField")) {
                        buildTable.setDisabled(true);
                    }
                } else if (comp.getId().equals("workbenchTable_toolbarbutton_buildKey")) {
                    buildKey = (Button) comp;
                    if (!loginUserVo.isAuthorized("tableKey")) {
                        buildKey.setDisabled(true);
                    }
                } else if (comp.getId().equals("workbenchTable_toolbarbutton_buildIndex")) {
                    buildIndex = (Button) comp;
                    if (!loginUserVo.isAuthorized("tableIndex")) {
                        buildIndex.setDisabled(true);
                    }
                } else if (comp.getId().equals("workbenchTable_toolbarbutton_buildFk")) {
                    buildFk = (Button) comp;
                    if (!loginUserVo.isAuthorized("tableFk")) {
                        buildFk.setDisabled(true);
                    }
                } else if (comp.getId().equals("workbenchTable_toolbarbutton_revert")) {
                    revert = (Button) comp;
                    if (!loginUserVo.isAuthorized("tableRevert")) {
                        revert.setDisabled(true);
                    }
                } else if (comp.getId().equals("workbenchTable_toolbarbutton_delete")) {
                    delete = (Button) comp;
                    if (!loginUserVo.isAuthorized("tableDel")) {
                        delete.setDisabled(true);
                    }
                } else if (comp.getId().equals("workbenchTable_toolbarbutton_scanAll"))
                    scanAll = (Button) comp;
                else if (comp.getId().equals("workbenchTable_toolbarbutton_importExcel") && loginUserVo.isAuthorized("tableImport")) {
                    importExcel = (Button) comp;
                    importExcel.addEventListener(Events.ON_UPLOAD, this);
                    if (Contexts.getProject().getMode() == ModeType.DEVELOP) {
                        importExcel.setDisabled(false);
                    }
                } else if (comp.getId().equals("workbenchTable_toolbarbutton_exportExcel") && loginUserVo.isAuthorized("tableExport")) {
                    exportExcel = (Button) comp;
                    if (Contexts.getProject().getMode() == ModeType.DEVELOP) {
                        exportExcel.setDisabled(false);
                    }
                } else if (comp.getId().equals("workbenchTable_toolbarbutton_exportTemp")) {
                    exportTemp = (Button) comp;
                    exportTemp.addEventListener(Events.ON_CLICK, new EventListener<Event>() {
                        @Override
                        public void onEvent(Event event) throws Exception {
                            Filedownload.save("~./excel/tables_temp.xls", null);
                        }
                    });
                    if (Contexts.getProject().getMode() == ModeType.DEVELOP) {
                        exportTemp.setDisabled(false);
                    }
                }
                comp.addEventListener(Events.ON_CLICK, this);
            } else if (comp.getId().equals("workbenchTable_bandbox_search")) {
                search = (Bandbox) comp;
                comp.addEventListener(Events.ON_OK, this);
                comp.addEventListener(Events.ON_OPEN, this);
            }
        }
        tables.addEventListener("onLater", this);
        is.getFirstChild().getLastChild()
                .addEventListener(Events.ON_AFTER_SIZE, this);
        Clients.showBusy(Labels.getLabel("message.long.operation"));
        Events.echoEvent("onLater", tables, null);
    }

    //导入excel参数表字段验证
    private boolean validate(TableBean entity) {
        if (StudioApp.execute(new CheckCmd(entity.getId()))) {
            WebUtils.showError(Labels.getLabel("entity.exists.id",
                    new String[]{entity.getId()}));
            return false;
        }
        if (Strings.isBlank(entity.getName())) {
            WebUtils.notEmpty(Labels.getLabel("entity.name"));
            return false;
        }
        if (entity.getKey() == null || entity.getKey().isEmpty()) {
            WebUtils.showError(Labels.getLabel("entity.table.key.set"));
            return false;
        }
        if (entity.getFields() == null || entity.getFields().isEmpty()) {
            WebUtils.showError(Labels.getLabel("entity.table.field.set"));
            return false;
        }
        if (entity.isAutoKey()) {
            if (entity.getKey().size() == 1) {
                String key = entity.getKey().get(0);
                for (TableField field : entity.getFields()) {
                    if (field.getName().equals(key)) {
                        if (field.getType() != FieldType.INT
                                && field.getType() != FieldType.LONG
                                && field.getType() != FieldType.NUMERIC) {
                            WebUtils.showError(Labels
                                    .getLabel("entity.table.auto.key.error"));
                            return false;
                        }
                        break;
                    }
                }
            } else {
                WebUtils.showError(Labels
                        .getLabel("entity.table.auto.key.error"));
                return false;
            }
        }
        for (TableField field : entity.getFields()) {
            if (Strings.isBlank(field.getName())) {
                WebUtils.showError(Labels
                        .getLabel("entity.table.field.name.not.empty"));
                return false;
            }
        }
        if (entity.getIndexes() != null) {
            if (entity.getIndexes().isEmpty()) {
                entity.setIndexes(null);
            } else {
                for (TableIndex index : entity.getIndexes()) {
                    if (Strings.isBlank(index.getName())) {
                        WebUtils.notEmpty(Labels
                                .getLabel("entity.table.index.name"));
                        return false;
                    }
                    if (Strings.isBlank(index.getFields())) {
                        WebUtils.notEmpty(Labels
                                .getLabel("entity.table.index.field"));
                        return false;
                    }
                }
            }
        }
        if (entity.getForeignKeys() != null) {
            if (entity.getForeignKeys().isEmpty()) {
                entity.setForeignKeys(null);
            } else {
                for (TableFk fk : entity.getForeignKeys()) {
                    if (Strings.isBlank(fk.getName())) {
                        WebUtils.notEmpty(Labels
                                .getLabel("entity.table.fk.name"));
                        return false;
                    }
                    if (Strings.isBlank(fk.getSourceFields())) {
                        WebUtils.notEmpty(Labels
                                .getLabel("entity.table.fk.field"));
                        return false;
                    }
                    if (Strings.isBlank(fk.getReferences())) {
                        WebUtils.notEmpty(Labels
                                .getLabel("entity.table.fk.table"));
                        return false;
                    }
                    if (Strings.isBlank(fk.getToFields())) {
                        WebUtils.notEmpty(Labels.getLabel("entity.table.fk.to"));
                        return false;
                    }
                    if (StringUtils.countMatches(fk.getSourceFields(), ",") != StringUtils
                            .countMatches(fk.getToFields(), ",")) {// 栏位数不匹配
                        WebUtils.showError(Labels
                                .getLabel("entity.table.fk.field.match"));
                        return false;
                    }
                }
            }
        }
        return true;
    }

    /**
     * 对应数据源下的参数表
     *
     * @param sv
     */
    private void createList(ScanVo sv) {
        List<TableVo> data = sv.getData();
        tables.getItems().clear();

        // 显示新增的参数表
        for (TableVo tv : sv.getSourceNew()) {
            Listitem li = new Listitem();
            li.setValue(tv);
            li.appendChild(new Listcell(tv.getId()));
            li.appendChild(new Listcell(tv.getName()));
            Listcell c = new Listcell();
            c.setIconSclass("z-icon-repeat");
            li.appendChild(c);
            tables.appendChild(li);
        }
        // 显示未建参数的真实表
        if (sv.getTargetNew() != null) {
            for (TableVo tv : sv.getTargetNew()) {
                Listitem li = new Listitem();
                li.setValue(tv);
                li.appendChild(new Listcell(tv.getId()));
                li.appendChild(new Listcell(tv.getName()));
                Listcell c = new Listcell();
                c.setIconSclass("z-icon-rotate-left");
                li.appendChild(c);
                tables.appendChild(li);
            }
        }
        // 其它正常或有异动的表
        for (int i = 0; i < data.size(); i++) {
            TableVo tv = data.get(i);
            Listitem li = new Listitem();
            li.setValue(tv);
            li.appendChild(new Listcell(tv.getId()));
            li.appendChild(new Listcell(tv.getName()));
            Listcell c = new Listcell();
            c.setIconSclass(tv.getState() == TableVo.DIFF ? "z-icon-gear"
                    : "z-icon-check");
            li.appendChild(c);
            tables.appendChild(li);
        }
    }

    /**
     * 显示表信息
     *
     * @param tv
     */
    private void showTable(TableVo tv) {
        TableBean source = tv.getSource();
        TableBean target = tv.getTarget();
        if (tv.getState() == TableVo.NORMAL || tv.getState() == TableVo.DIFF) {
            createTableInfo(sourceTable, source, tv.getDiffFields(),
                    tv.getSourceNeFields());
            createTableInfo(targetTable, target, tv.getDiffFields(),
                    tv.getTargetNewFields());
            if (!loginUserVo.isAuthorized("tableCreate")) {
                create.setDisabled(true);
            } else {
                create.setDisabled(true);
            }
            if (!loginUserVo.isAuthorized("tableField")) {
                buildTable.setDisabled(true);
            } else {
                buildTable.setDisabled(!tv.isDiffByField());
            }
            if (!loginUserVo.isAuthorized("tableKey")) {
                buildKey.setDisabled(true);
            } else {
                buildKey.setDisabled(!tv.isDiffByKey());
            }
            if (!loginUserVo.isAuthorized("tableFk")) {
                buildFk.setDisabled(true);
            } else {
                buildFk.setDisabled(!tv.isDiffByFk());
            }
            if (!loginUserVo.isAuthorized("tableIndex")) {
                buildIndex.setDisabled(true);
            } else {
                buildIndex.setDisabled(!tv.isDiffByIndex());
            }
            if (!loginUserVo.isAuthorized("tableDel")) {
                delete.setDisabled(true);
            } else {
                delete.setDisabled(false);
            }
            if (!loginUserVo.isAuthorized("tableRevert")) {
                revert.setDisabled(true);
            } else {
                revert.setDisabled(true);
            }
        } else if (tv.getState() == TableVo.NEW) {
            createTableInfo(sourceTable, source, null, null);
            targetTable.getRows().getChildren().clear();
            if (!loginUserVo.isAuthorized("tableCreate")) {
                create.setDisabled(true);
            } else {
                create.setDisabled(false);
            }
            if (!loginUserVo.isAuthorized("tableField")) {
                buildTable.setDisabled(true);
            } else {
                buildTable.setDisabled(true);
            }
            if (!loginUserVo.isAuthorized("tableKey")) {
                buildKey.setDisabled(true);
            } else {
                buildKey.setDisabled(true);
            }
            if (!loginUserVo.isAuthorized("tableFk")) {
                buildFk.setDisabled(true);
            } else {
                buildFk.setDisabled(true);
            }
            if (!loginUserVo.isAuthorized("tableIndex")) {
                buildIndex.setDisabled(true);
            } else {
                buildIndex.setDisabled(true);
            }
            if (!loginUserVo.isAuthorized("tableDel")) {
                delete.setDisabled(true);
            } else {
                delete.setDisabled(true);
            }
            if (!loginUserVo.isAuthorized("tableRevert")) {
                revert.setDisabled(true);
            } else {
                revert.setDisabled(true);
            }
        } else if (tv.getState() == TableVo.REVERT) {
            createTableInfo(targetTable, target, null, null);
            sourceTable.getRows().getChildren().clear();
            if (!loginUserVo.isAuthorized("tableCreate")) {
                create.setDisabled(true);
            } else {
                create.setDisabled(true);
            }
            if (!loginUserVo.isAuthorized("tableField")) {
                buildTable.setDisabled(true);
            } else {
                buildTable.setDisabled(true);
            }
            if (!loginUserVo.isAuthorized("tableKey")) {
                buildKey.setDisabled(true);
            } else {
                buildKey.setDisabled(true);
            }
            if (!loginUserVo.isAuthorized("tableFk")) {
                buildFk.setDisabled(true);
            } else {
                buildFk.setDisabled(true);
            }
            if (!loginUserVo.isAuthorized("tableIndex")) {
                buildIndex.setDisabled(true);
            } else {
                buildIndex.setDisabled(true);
            }
            if (!loginUserVo.isAuthorized("tableDel")) {
                delete.setDisabled(true);
            } else {
                delete.setDisabled(false);
            }
            if (!loginUserVo.isAuthorized("tableRevert")) {
                revert.setDisabled(true);
            } else {
                revert.setDisabled(false);
            }
        }
    }

    /**
     * 创建具体表信息
     *
     * @param grid
     * @param tb
     */
    private void createTableInfo(Grid grid, TableBean tb,
                                 List<String> diffFields, List<String> newFields) {
        grid.getRows().getChildren().clear();
        Group g = new Group(Labels.getLabel("entity.table.field"));
        grid.getRows().appendChild(g);
        for (TableField tf : tb.getFields()) {
            Row row = new Row();
            String name = tf.getName().toUpperCase();
            if (diffFields != null && diffFields.contains(name)) {
                Label label = new Label("*" + name);
                label.setSclass("epeditor-btn-mark");
                label.setParent(row);
            } else if (newFields != null && newFields.contains(name)) {
                Label label = new Label("+" + name);
                label.setSclass("epeditor-btn-mark");
                label.setParent(row);
            } else
                row.appendChild(new Label(tf.getName()));
            if (grid == sourceTable)
                row.appendChild(new Label(tf.getDescription()));
            row.appendChild(new Label(tf.getType().name()));
            row.appendChild(new Label(tf.getLength() + ""));
            row.appendChild(new Label(tf.getDecimal() + ""));
            grid.getRows().appendChild(row);
        }
        if (tb.getKey() != null && !tb.getKey().isEmpty())
            appendPrimaryKeyRow(grid, tb);

        if (tb.getIndexes() != null && !tb.getIndexes().isEmpty())
            appendIndexRow(grid, tb);

        if (tb.getForeignKeys() != null && !tb.getForeignKeys().isEmpty())
            appendFkRow(grid, tb);

        if (tb.isByDb() && tb.getKey() != null && !tb.getKey().isEmpty())
            appendAutoRow(grid, tb);
    }

    private void appendPrimaryKeyRow(Grid grid, TableBean tb) {
        Group g = new Group(Labels.getLabel("entity.id"));
        grid.getRows().appendChild(g);
        for (String field : tb.getKey()) {
            for (TableField tf : tb.getFields()) {
                if (tf.getName().equals(field)) {
                    Row row = new Row();
                    row.appendChild(new Label(tf.getName()));
                    if (grid == sourceTable)
                        row.appendChild(new Label(tf.getDescription()));
                    row.appendChild(new Label(tf.getType().name()));
                    row.appendChild(new Label(tf.getLength() + ""));
                    row.appendChild(new Label(tf.getDecimal() + ""));
                    grid.getRows().appendChild(row);
                    break;
                }
            }
        }
    }

    private void appendAutoRow(Grid grid, TableBean tb) {
        Group g = new Group(Labels.getLabel("entity.table.auto"));
        grid.getRows().appendChild(g);
        for (String field : tb.getKey()) {
            for (TableField tf : tb.getFields()) {
                if (tf.getName().equals(field)) {
                    Row row = new Row();
                    row.appendChild(new Label(tf.getName()));
                    if (grid == sourceTable)
                        row.appendChild(new Label(tf.getDescription()));
                    row.appendChild(new Label(tf.getType().name()));
                    row.appendChild(new Label(tf.getLength() + ""));
                    row.appendChild(new Label(tf.getDecimal() + ""));
                    grid.getRows().appendChild(row);
                    break;
                }
            }
        }
    }

    private void appendIndexRow(Grid grid, TableBean tb) {
        for (TableIndex idx : tb.getIndexes()) {
            if (idx.getFields() != null) {
                Group g = new Group(Labels.getLabel("entity.table.indexes")
                        + ":" + idx.getName());
                grid.getRows().appendChild(g);
                for (String field : idx.getFields().split(",")) {
                    for (TableField tf : tb.getFields()) {
                        if (tf.getName().equalsIgnoreCase(field)) {
                            Row row = new Row();
                            row.appendChild(new Label(tf.getName()));
                            if (grid == sourceTable)
                                row.appendChild(new Label(tf.getDescription()));
                            row.appendChild(new Label(tf.getType().name()));
                            row.appendChild(new Label(tf.getLength() + ""));
                            row.appendChild(new Label(tf.getDecimal() + ""));
                            grid.getRows().appendChild(row);
                            break;
                        }
                    }// for
                }// for
            }// for
        }
    }

    private void appendFkRow(Grid grid, TableBean tb) {
        for (TableFk fk : tb.getForeignKeys()) {
            Group g = new Group(Labels.getLabel("entity.table.fks") + ":"
                    + fk.getName());
            grid.getRows().appendChild(g);
            Row row = new Row();
            row.appendChild(new Label(fk.getSourceFields()));
            row.appendChild(new Label(fk.getReferences()));
            row.appendChild(new Label(fk.getToFields()));
            row.appendChild(new Label(fk.getAction()));
            grid.getRows().appendChild(row);
        }
    }

    @Override
    public void dispatch(Event event) {
        if (event.getName().equals("onLater")) {
            try {
                if (event.getData() == null
                        || event.getData() instanceof String) {
                    disableToolbar();
                    ScanVo cv = StudioApp.execute(new ScanCmd(datasource
                            .getSelectedItem().getLabel(),
                            event.getData() == null ? paging.getPageSize() : 0,
                            1, search.getValue(), true));
                    if (event.getData() == null)
                        paging.setTotalSize(cv.getTotalSize());
                    else
                        paging.setTotalSize(0);
                    createList(cv);
                } else if (event.getData() instanceof PagingEvent) {
                    disableToolbar();
                    PagingEvent pe = (PagingEvent) event.getData();
                    ScanVo cv = StudioApp.execute(new ScanCmd(datasource
                            .getSelectedItem().getLabel(),
                            paging.getPageSize(), pe.getActivePage() + 1,
                            search.getValue(), false));
                    createList(cv);
                } else {
                    TableVo tv = (TableVo) event.getData();
                    if (StudioApp.execute(new BuildTableCmd(tv))) {
                        sourceTable.getRows().getChildren().clear();
                        targetTable.getRows().getChildren().clear();
                        tv.setTarget(tv.getSource());
                        createTableInfo(sourceTable, tv.getSource(), null, null);
                        createTableInfo(targetTable, tv.getTarget(), null, null);
                        if (!loginUserVo.isAuthorized("tableField")) {
                            buildTable.setDisabled(true);
                        } else {
                            buildTable.setDisabled(true);
                        }
                        tv.setDiffByField(false);
                        tv.setDiffByFk(false);
                        tv.setDiffByIndex(false);
                        tv.setDiffByKey(false);
                        if (tv.getDiffFields() != null)
                            tv.getDiffFields().clear();
                        if (tv.getSourceNeFields() != null)
                            tv.getSourceNeFields().clear();
                        if (tv.getTargetNewFields() != null)
                            tv.getTargetNewFields().clear();
                        tv.setState(TableVo.NORMAL);
                        ((Listcell) tables.getSelectedItem().getLastChild())
                                .setIconSclass("z-icon-check");
                    }
                }
            } finally {
                Clients.clearBusy();
            }
        } else if (event.getName().equals(Events.ON_AFTER_SIZE)) {
            AfterSizeEvent ase = (AfterSizeEvent) event;
            ((East) targetTable.getParent().getParent()).setWidth((ase.getWidth() / 2)
                    + "px");
        } else if (event.getName().equals(ZulEvents.ON_PAGING)) {
            Clients.showBusy(Labels.getLabel("message.long.operation"));
            Events.echoEvent("onLater", tables, event);
        } else if (event.getName().equals(Events.ON_UPLOAD)) {
            if (event.getTarget().getId().equals("workbenchTable_toolbarbutton_importExcel")) {
                UploadEvent evt = (UploadEvent) event;
                org.zkoss.util.media.Media media = evt.getMedia();
                System.out.println(media.getContentType());
                if ("application/vnd.ms-excel".equals(media.getContentType()) ||
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet".equals(media.getContentType())) {
                    List<TableBean> tableBeans = null;
                    try {
                        if ("application/vnd.ms-excel".equals(media.getContentType())) {
                            tableBeans = (List<TableBean>) new ImportExecl().read(new ByteArrayInputStream(media.getByteData()), true, ImportExecl.TABLE).get("tableBeans");
                        } else {
                            tableBeans = (List<TableBean>) new ImportExecl().read(new ByteArrayInputStream(media.getByteData()), false, ImportExecl.TABLE).get("tableBeans");
                        }

                        AbstractView.createExcelImportView(
                                new EditorCallback<List<Object>>() {

                                    @Override
                                    public List<Object> getValue() {
                                        return null;
                                    }

                                    @Override
                                    public void setValue(List<Object> dataList) {
                                        for (Object object : dataList) {
                                            TableBean tableBean = (TableBean) object;
                                            if (!validate(tableBean)) {
                                                return;
                                            }
                                        }
                                        for (Object object : dataList) {
                                            TableBean tableBean = (TableBean) object;
                                            tableBean.setType("Table");
                                            StudioUtil.save(tableBean, 'C');
                                            String insertSql = "INSERT INTO sys_table_field_info(table1id,table1name,table1description,field1name,field1type,field1length,field1decimal,field1description,field1notnull,field1orderNo,field1acc,field1defaultvalue) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
                                            List<FieldVo> params = new ArrayList<FieldVo>();
                                            int i = 1;
                                            for (TableField tf : tableBean.getFields()) {
                                                params.clear();
                                                params.add(new FieldVo(FieldType.VARCHAR, tableBean.getId()));//表名
                                                params.add(new FieldVo(FieldType.VARCHAR, tableBean.getName()));//表中文名
                                                params.add(new FieldVo(FieldType.VARCHAR, tableBean.getDescription()));//表描述
                                                params.add(new FieldVo(FieldType.VARCHAR, tf.getName()));//字段名
                                                params.add(new FieldVo(FieldType.VARCHAR, tf.getType()));//字段类型
                                                params.add(new FieldVo(FieldType.VARCHAR, tf.getLength()));//字段长度
                                                params.add(new FieldVo(FieldType.VARCHAR, tf.getDecimal()));//小数位
                                                params.add(new FieldVo(FieldType.VARCHAR, tf.getDescription()));//字段说明
                                                params.add(new FieldVo(FieldType.VARCHAR, tf.isNotNull()));//是否可为空
                                                params.add(new FieldVo(FieldType.VARCHAR, i + ""));//字段顺序
                                                params.add(new FieldVo(FieldType.VARCHAR, tf.getAcc()));//关联字段
                                                params.add(new FieldVo(FieldType.VARCHAR, tf.getDefaultValue()));//字段默认值
                                                StudioApp.execute(new UpdateCmd(null, insertSql, params));
                                                i++;
                                            }
                                        }
                                        WebUtils.showInfo(Labels.getLabel("message.success",
                                                new String[]{Labels.getLabel("editor.import.title")}));
                                    }

                                    @Override
                                    public String getTitle() {
                                        return Labels.getLabel("editor.table.build.excel.import");
                                    }

                                    @Override
                                    public Page getPage() {
                                        return exportExcel.getPage();
                                    }

                                    @Override
                                    public Editor getEditor() {
                                        return null;
                                    }
                                }
                                , tableBeans, "TABLE", event.getTarget()).doOverlapped();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                } else if (media != null) {
                    Messagebox.show("Not an excel: " + media, "Error", Messagebox.OK, Messagebox.ERROR);
                }
            }
        } else {
            Component c = event.getTarget();
            if (c == datasource || c == search) {
                if (c == datasource)
                    search.setValue("");
                else if (event instanceof OpenEvent) {
                    OpenEvent evt = (OpenEvent) event;
                    search.setRawValue(evt.getValue());
                }
                paging.setActivePage(0);
                Clients.showBusy(Labels.getLabel("message.long.operation"));
                Events.echoEvent("onLater", tables, null);
            } else if (c == tables) {
                if (tables.getSelectedIndex() >= 0) {
                    TableVo tv = tables.getSelectedItem().getValue();
                    showTable(tv);
                }
            } else if (c == create) {
                TableVo tv = tables.getSelectedItem().getValue();
                boolean s = StudioApp
                        .execute(new CreateTableCmd(tv.getSource()));
                if (s) {
                    tv.setState(TableVo.NORMAL);
                    tv.setTarget(tv.getSource());
                    createTableInfo(targetTable, tv.getTarget(), null, null);
                    if (!loginUserVo.isAuthorized("tableDel")) {
                        delete.setDisabled(true);
                    } else {
                        delete.setDisabled(false);
                    }
                    if (!loginUserVo.isAuthorized("tableCreate")) {
                        create.setDisabled(true);
                    } else {
                        create.setDisabled(true);
                    }
                    WebUtils.showSuccess(Labels.getLabel("editor.table.create"));
                    ((Listcell) tables.getSelectedItem().getLastChild())
                            .setIconSclass("z-icon-check");
                }
            } else if (c == buildTable) {
                buildTable();
            } else if (c == buildIndex) {
                buildIndex();
            } else if (c == buildKey) {
                buildKey();
            } else if (c == buildFk) {
                buildFk();
            } else if (c == revert) {
                revert();
            } else if (c == delete) {
                delete();
            } else if (c == scanAll) {
                Clients.showBusy(Labels.getLabel("message.long.running",
                        new String[]{scanAll.getLabel()}));
                Events.echoEvent("onLater", tables, "scanAll");
            } else if (c == exportExcel) {
                AbstractView.createTableMultipleView(
                        new EditorCallback<List<TableBean>>() {
                            @Override
                            public List<TableBean> getValue() {
                                return null;
                            }

                            @Override
                            public void setValue(List<TableBean> value) {
                                if (value == null || value.size() == 0) {
                                    return;
                                }
                                HSSFWorkbook wb = new HSSFWorkbook();
                                for (TableBean tableBean : value) {
                                    String[] sheetOneTitle = {"序号", "栏位名称", "类型", "长度", "小数位", "不可为空", "描述", "默认值", "关联栏位", "主键"};
                                    String sheetOnecontent[][] = new String[tableBean.getFields().size()][sheetOneTitle.length];
                                    int keyIndex = 1;
                                    for (int i = 0; i < tableBean.getFields().size(); i++) {
                                        TableField tableField = tableBean.getFields().get(i);
                                        sheetOnecontent[i][0] = (i + 1) + "";
                                        sheetOnecontent[i][1] = tableField.getName();
                                        sheetOnecontent[i][2] = tableField.getType().name();
                                        sheetOnecontent[i][3] = tableField.getLength() + "";
                                        sheetOnecontent[i][4] = tableField.getDecimal() + "";
                                        sheetOnecontent[i][5] = tableField.isNotNull() + "";
                                        sheetOnecontent[i][6] = tableField.getDescription();
                                        sheetOnecontent[i][7] = tableField.getDefaultValue();
                                        sheetOnecontent[i][8] = tableField.getAcc();
                                        sheetOnecontent[i][9] = tableBean.getKey().indexOf(tableField.getName()) != -1 ? "" + (keyIndex++) : "";
                                    }
                                    String[] sheetTwoTitle = {"索引名称", "是否唯一", "栏位"};
                                    if (tableBean.getIndexes() == null)
                                        tableBean.setIndexes(new ArrayList<TableIndex>());
                                    String sheetTwocontent[][] = new String[tableBean.getIndexes().size()][sheetTwoTitle.length];
                                    for (int i = 0; i < tableBean.getIndexes().size(); i++) {
                                        TableIndex tableIndex = tableBean.getIndexes().get(i);
                                        sheetTwocontent[i][0] = tableIndex.getName();
                                        sheetTwocontent[i][1] = tableIndex.isUnique() + "";
                                        sheetTwocontent[i][2] = tableIndex.getFields();
                                    }
                                    String[] sheetThreeTitle = {"外键名称", "栏位", "目标表", "目标表栏位", "关联操作"};
                                    if (tableBean.getForeignKeys() == null)
                                        tableBean.setForeignKeys(new ArrayList<TableFk>());
                                    String sheetThreecontent[][] = new String[tableBean.getForeignKeys().size()][sheetThreeTitle.length];
                                    for (int i = 0; i < tableBean.getForeignKeys().size(); i++) {
                                        TableFk tableFk = tableBean.getForeignKeys().get(i);
                                        sheetThreecontent[i][0] = tableFk.getName();
                                        sheetThreecontent[i][1] = tableFk.getSourceFields();
                                        sheetThreecontent[i][2] = tableFk.getReferences();
                                        sheetThreecontent[i][3] = tableFk.getToFields();
                                        sheetThreecontent[i][4] = tableFk.getAction();
                                    }
                                    String[] sheetFiveTitle = {"表名", "名称", "自动生成主键值"};
                                    String sheetFivecontent[][] = new String[1][sheetFiveTitle.length];
                                    sheetFivecontent[0][0] = tableBean.getId() + "";
                                    sheetFivecontent[0][1] = tableBean.getName() + "";
                                    sheetFivecontent[0][2] = tableBean.isAutoKey() + "";
                                    List<Map<String, Object>> data = new ArrayList<>();
                                    Map<String, Object> one = new HashMap<String, Object>();
                                    one.put("title", sheetOneTitle);
                                    one.put("values", sheetOnecontent);
                                    Map<String, Object> two = new HashMap<String, Object>();
                                    two.put("title", sheetTwoTitle);
                                    two.put("values", sheetTwocontent);
                                    Map<String, Object> three = new HashMap<String, Object>();
                                    three.put("title", sheetThreeTitle);
                                    three.put("values", sheetThreecontent);
                                    Map<String, Object> five = new HashMap<String, Object>();
                                    five.put("title", sheetFiveTitle);
                                    five.put("values", sheetFivecontent);
                                    data.add(five);
                                    data.add(one);
                                    data.add(two);
                                    data.add(three);
                                    ExportExcel.getHSSFWorkbook(tableBean.getId() + "", data, wb);
                                }
                                try {
                                    File tempFile = File.createTempFile("tables", ".xls");
                                    FileOutputStream os = new FileOutputStream(tempFile);
                                    wb.write(os);
                                    os.flush();
                                    os.close();
                                    Filedownload.save(tempFile, "application/vnd.ms-excel");
                                    tempFile.deleteOnExit();
                                } catch (IOException e) {
                                    e.printStackTrace();
                                }
                            }

                            @Override
                            public String getTitle() {
                                return Labels.getLabel("editor.table.build.excel.export");
                            }

                            @Override
                            public Page getPage() {
                                return exportExcel.getPage();
                            }

                            @Override
                            public Editor getEditor() {
                                return null;
                            }
                        }, c).doOverlapped();

            }
        }
    }

    private void disableToolbar() {
        targetTable.getRows().getChildren().clear();
        sourceTable.getRows().getChildren().clear();
        if (!loginUserVo.isAuthorized("tableCreate")) {
            create.setDisabled(true);
        } else {
            create.setDisabled(true);
        }
        if (!loginUserVo.isAuthorized("tableField")) {
            buildTable.setDisabled(true);
        } else {
            buildTable.setDisabled(true);
        }
        if (!loginUserVo.isAuthorized("tableKey")) {
            buildKey.setDisabled(true);
        } else {
            buildKey.setDisabled(true);
        }
        if (!loginUserVo.isAuthorized("tableFk")) {
            buildFk.setDisabled(true);
        } else {
            buildFk.setDisabled(true);
        }
        if (!loginUserVo.isAuthorized("tableIndex")) {
            buildIndex.setDisabled(true);
        } else {
            buildIndex.setDisabled(true);
        }
        if (!loginUserVo.isAuthorized("tableDel")) {
            delete.setDisabled(true);
        } else {
            delete.setDisabled(true);
        }
        if (!loginUserVo.isAuthorized("tableRevert")) {
            revert.setDisabled(true);
        } else {
            revert.setDisabled(true);
        }
    }

    private void delete() {
        final TableVo tv = tables.getSelectedItem().getValue();
        WebUtils.showConfirm(Labels.getLabel("editor.table.delete.confirm",
                new Object[]{tv.getTarget().getId()}),
                new EventListener<Event>() {
                    @Override
                    public void onEvent(Event event) throws Exception {
                        if (event.getName().equals(Events.ON_OK)) {
                            if (StudioApp.execute(new DropTableCmd(tv
                                    .getTarget()))) {
                                if (tv.getState() == TableVo.REVERT) {
                                    tables.getSelectedItem().detach();
                                    if (!loginUserVo.isAuthorized("tableCreate")) {
                                        create.setDisabled(true);
                                    } else {
                                        create.setDisabled(true);
                                    }
                                } else {
                                    tv.setState(TableVo.NEW);
                                    ((Listcell) tables.getSelectedItem()
                                            .getLastChild())
                                            .setIconSclass("z-icon-repeat");
                                    if (!loginUserVo.isAuthorized("tableCreate")) {
                                        create.setDisabled(true);
                                    } else {
                                        create.setDisabled(false);
                                    }
                                }
                                if (!loginUserVo.isAuthorized("tableField")) {
                                    buildTable.setDisabled(true);
                                } else {
                                    buildTable.setDisabled(true);
                                }
                                if (!loginUserVo.isAuthorized("tableKey")) {
                                    buildKey.setDisabled(true);
                                } else {
                                    buildKey.setDisabled(true);
                                }
                                if (!loginUserVo.isAuthorized("tableFk")) {
                                    buildFk.setDisabled(true);
                                } else {
                                    buildFk.setDisabled(true);
                                }
                                if (!loginUserVo.isAuthorized("tableIndex")) {
                                    buildIndex.setDisabled(true);
                                } else {
                                    buildIndex.setDisabled(true);
                                }
                                if (!loginUserVo.isAuthorized("tableDel")) {
                                    delete.setDisabled(true);
                                } else {
                                    delete.setDisabled(true);
                                }
                                if (!loginUserVo.isAuthorized("tableRevert")) {
                                    revert.setDisabled(true);
                                } else {
                                    revert.setDisabled(true);
                                }
                                targetTable.getRows().getChildren().clear();
                            }
                        }
                    }
                });
    }

    private void revert() {
        TableVo tv = tables.getSelectedItem().getValue();
        TableBean entity = tv.getTarget();
        entity.setName(entity.getId());
        EntityInfo ei = new EntityInfo();
        StringBuilder sb = new StringBuilder();
        StringOutputStream os = new StringOutputStream(sb);
        TransformerFactory.newInstance().transformToXml(entity, os);
        ei.setContent(sb.toString());
        ei.setId(entity.getId());
        ei.setName(entity.getName());
        ei.setDescription(entity.getDescription());
        ei.setType(EntityType.TABLE.getName());
        ei.setSubType(entity.getSubType());
        ei.setStatus('C');
        if (StudioApp.execute(new SaveCmd(ei))) {
            String insertSql = "INSERT INTO sys_table_field_info(table1id,table1name,table1description,field1name,field1type,field1length,field1decimal,field1description,field1notnull,field1orderNo,field1acc,field1defaultvalue) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
            List<FieldVo> params = new ArrayList<FieldVo>();
            int i = 1;
            for (TableField tf : entity.getFields()) {
                params.clear();
                params.add(new FieldVo(FieldType.VARCHAR, entity.getId()));//表名
                params.add(new FieldVo(FieldType.VARCHAR, entity.getName()));//表中文名
                params.add(new FieldVo(FieldType.VARCHAR, entity.getDescription()));//表描述
                params.add(new FieldVo(FieldType.VARCHAR, tf.getName()));//字段名
                params.add(new FieldVo(FieldType.VARCHAR, tf.getType()));//字段类型
                params.add(new FieldVo(FieldType.VARCHAR, tf.getLength()));//字段长度
                params.add(new FieldVo(FieldType.VARCHAR, tf.getDecimal()));//小数位
                params.add(new FieldVo(FieldType.VARCHAR, tf.getDescription()));//字段说明
                params.add(new FieldVo(FieldType.VARCHAR, tf.isNotNull()));//是否可为空
                params.add(new FieldVo(FieldType.VARCHAR, i + ""));//字段顺序
                params.add(new FieldVo(FieldType.VARCHAR, tf.getAcc()));//关联字段
                params.add(new FieldVo(FieldType.VARCHAR, tf.getDefaultValue()));//字段默认值
                StudioApp.execute(new UpdateCmd(entity.getSubType(), insertSql, params));
                i++;
            }
            tv.setState(TableVo.NORMAL);
            tv.setSource(entity);
            createTableInfo(sourceTable, entity, null, null);
            if (!loginUserVo.isAuthorized("tableRevert")) {
                revert.setDisabled(true);
            } else {
                revert.setDisabled(true);
            }
            WebUtils.showSuccess(Labels.getLabel("editor.table.revert"));
            ((Listcell) tables.getSelectedItem().getLastChild())
                    .setIconSclass("z-icon-check");
        }
    }

    private void buildTable() {
        WebUtils.showConfirm(Labels.getLabel("editor.table.build.field"),
                new EventListener<Event>() {
                    @Override
                    public void onEvent(Event event) throws Exception {
                        if (event.getName().equals(Events.ON_OK)) {
                            Clients.showBusy(Labels
                                    .getLabel("message.long.operation"));
                            Events.echoEvent("onLater", tables, (TableVo)tables
                                    .getSelectedItem().getValue());
                        }
                    }
                });
    }

    private void buildIndex() {
        final TableVo tv = tables.getSelectedItem().getValue();
        WebUtils.showConfirm(Labels.getLabel("editor.table.build.index",
                new Object[]{tv.getTarget().getId()}),
                new EventListener<Event>() {
                    @Override
                    public void onEvent(Event event) throws Exception {
                        if (event.getName().equals(Events.ON_OK)) {
                            if (StudioApp.execute(new BuildIndexCmd(tv))) {
                                targetTable.getRows().getChildren().clear();
                                if (tv.getTarget().getIndexes() == null)
                                    tv.getTarget().setIndexes(
                                            new ArrayList<TableIndex>());
                                else
                                    tv.getTarget().getIndexes().clear();
                                if (tv.getSource().getIndexes() != null)
                                    tv.getTarget()
                                            .getIndexes()
                                            .addAll(tv.getSource().getIndexes());
                                createTableInfo(targetTable, tv.getTarget(),
                                        null, null);
                                if (!loginUserVo.isAuthorized("tableIndex")) {
                                    buildIndex.setDisabled(true);
                                } else {
                                    buildIndex.setDisabled(true);
                                }
                                tv.setDiffByIndex(false);
                                if (!tv.isDiffByField() && !tv.isDiffByKey()
                                        && !tv.isDiffByFk()) {
                                    tv.setState(TableVo.NORMAL);
                                    ((Listcell) tables.getSelectedItem()
                                            .getLastChild())
                                            .setIconSclass("z-icon-check");
                                }
                            }
                        }
                    }
                });
    }

    private void buildKey() {
        final TableVo tv = tables.getSelectedItem().getValue();
        WebUtils.showConfirm(Labels.getLabel("editor.table.build.key"),
                new EventListener<Event>() {
                    @Override
                    public void onEvent(Event event) throws Exception {
                        if (event.getName().equals(Events.ON_OK)) {
                            if (StudioApp.execute(new BuildKeyCmd(tv))) {
                                targetTable.getRows().getChildren().clear();
                                if (tv.getTarget().getKey() == null)
                                    tv.getTarget().setKey(
                                            new ArrayList<String>());
                                else
                                    tv.getTarget().getKey().clear();
                                if (tv.getSource().getKey() != null)
                                    tv.getTarget().getKey()
                                            .addAll(tv.getSource().getKey());
                                createTableInfo(targetTable, tv.getTarget(),
                                        null, null);
                                if (!loginUserVo.isAuthorized("tableKey")) {
                                    buildKey.setDisabled(true);
                                } else {
                                    buildKey.setDisabled(true);
                                }
                                tv.setDiffByKey(false);
                                if (!tv.isDiffByField() && !tv.isDiffByFk()
                                        && !tv.isDiffByIndex()) {
                                    tv.setState(TableVo.NORMAL);
                                    ((Listcell) tables.getSelectedItem()
                                            .getLastChild())
                                            .setIconSclass("z-icon-check");
                                }
                            }
                        }
                    }
                });
    }

    private void buildFk() {
        final TableVo tv = tables.getSelectedItem().getValue();
        WebUtils.showConfirm(Labels.getLabel("editor.table.build.fk",
                new Object[]{tv.getTarget().getId()}),
                new EventListener<Event>() {
                    @Override
                    public void onEvent(Event event) throws Exception {
                        if (event.getName().equals(Events.ON_OK)) {
                            if (StudioApp.execute(new BuildFkCmd(tv))) {
                                targetTable.getRows().getChildren().clear();
                                if (tv.getTarget().getForeignKeys() == null)
                                    tv.getTarget().setForeignKeys(
                                            new ArrayList<TableFk>());
                                else
                                    tv.getTarget().getForeignKeys().clear();
                                if (tv.getSource().getForeignKeys() != null)
                                    tv.getTarget()
                                            .getForeignKeys()
                                            .addAll(tv.getSource()
                                                    .getForeignKeys());
                                createTableInfo(targetTable, tv.getTarget(),
                                        null, null);
                                if (!loginUserVo.isAuthorized("tableFk")) {
                                    buildFk.setDisabled(true);
                                } else {
                                    buildFk.setDisabled(true);
                                }
                                tv.setDiffByFk(false);
                                if (!tv.isDiffByField() && !tv.isDiffByKey()
                                        && !tv.isDiffByIndex()) {
                                    tv.setState(TableVo.NORMAL);
                                    ((Listcell) tables.getSelectedItem()
                                            .getLastChild())
                                            .setIconSclass("z-icon-check");
                                }
                            }
                        }
                    }
                });
    }
}
